// MySQL数据库连接配置
import mysql from 'mysql2/promise';

// 数据库连接配置
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '3306'),
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'math_academy',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
};

// 创建连接池
let pool: mysql.Pool | null = null;

export function getPool(): mysql.Pool {
  if (!pool) {
    pool = mysql.createPool(dbConfig);
  }
  return pool;
}

// 获取数据库连接
export async function getConnection() {
  const pool = getPool();
  return await pool.getConnection();
}

// 执行查询
export async function query<T = any>(sql: string, params?: any[]): Promise<T> {
  const pool = getPool();
  const [rows] = await pool.execute(sql, params);
  return rows as T;
}

// 执行单条查询
export async function queryOne<T = any>(sql: string, params?: any[]): Promise<T | null> {
  const rows = await query<T[]>(sql, params);
  return rows.length > 0 ? rows[0] : null;
}

// 执行事务
export async function transaction<T>(
  callback: (connection: mysql.PoolConnection) => Promise<T>
): Promise<T> {
  const connection = await getConnection();
  try {
    await connection.beginTransaction();
    const result = await callback(connection);
    await connection.commit();
    return result;
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

// 测试数据库连接
export async function testConnection(): Promise<boolean> {
  try {
    const pool = getPool();
    const connection = await pool.getConnection();
    await connection.ping();
    connection.release();
    console.log('✅ MySQL数据库连接成功');
    return true;
  } catch (error) {
    console.error('❌ MySQL数据库连接失败:', error);
    return false;
  }
}

// 关闭连接池
export async function closePool(): Promise<void> {
  if (pool) {
    await pool.end();
    pool = null;
  }
}

// 用户相关数据库操作
export const UserDB = {
  // 创建用户
  async create(username: string, passwordHash: string, email?: string) {
    const connection = await getPool().getConnection();
    try {
      await connection.beginTransaction();

      // 创建用户
      const sql = `
        INSERT INTO users (username, password_hash, email, display_name)
        VALUES (?, ?, ?, ?)
      `;
      const [result] = await connection.execute(sql, [
        username,
        passwordHash,
        email || null,
        username,
      ]) as any;

      const userId = result.insertId;

      // 创建用户配置记录
      const profileSql = `
        INSERT INTO user_profiles (user_id, grade)
        VALUES (?, ?)
      `;
      await connection.execute(profileSql, [userId, 1]);

      await connection.commit();
      return userId;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  },

  // 根据用户名查找用户
  async findByUsername(username: string) {
    const sql = `
      SELECT id, username, email, password_hash, display_name, avatar_url, role, created_at, last_login_at
      FROM users
      WHERE username = ? AND is_active = TRUE
    `;
    return await queryOne(sql, [username]);
  },

  // 根据ID查找用户
  async findById(id: number) {
    const sql = `
      SELECT id, username, email, display_name, avatar_url, role, created_at, last_login_at
      FROM users
      WHERE id = ? AND is_active = TRUE
    `;
    return await queryOne(sql, [id]);
  },

  // 更新最后登录时间
  async updateLastLogin(id: number) {
    const sql = `UPDATE users SET last_login_at = NOW() WHERE id = ?`;
    await query(sql, [id]);
  },

  // 检查用户名是否存在
  async usernameExists(username: string): Promise<boolean> {
    const sql = `SELECT COUNT(*) as count FROM users WHERE username = ?`;
    const result = await queryOne<{ count: number }>(sql, [username]);
    return (result?.count || 0) > 0;
  },

  // 检查邮箱是否存在
  async emailExists(email: string): Promise<boolean> {
    const sql = `SELECT COUNT(*) as count FROM users WHERE email = ?`;
    const result = await queryOne<{ count: number }>(sql, [email]);
    return (result?.count || 0) > 0;
  },
};

// 学习进度相关数据库操作
export const ProgressDB = {
  // 获取用户所有进度
  async getUserProgress(userId: number) {
    const sql = `
      SELECT * FROM user_progress
      WHERE user_id = ?
      ORDER BY last_practiced_at DESC
    `;
    return await query(sql, [userId]);
  },

  // 获取用户特定题型进度
  async getTopicProgress(userId: number, topicId: string) {
    const sql = `
      SELECT * FROM user_progress
      WHERE user_id = ? AND topic_id = ?
    `;
    return await queryOne(sql, [userId, topicId]);
  },

  // 更新进度（使用存储过程）
  async updateProgress(
    userId: number,
    topicId: string,
    topicName: string,
    isCorrect: boolean,
    timeSpent: number
  ) {
    const sql = `CALL update_user_progress(?, ?, ?, ?, ?)`;
    await query(sql, [userId, topicId, topicName, isCorrect, timeSpent]);
  },
};

// 练习记录相关数据库操作
export const SessionDB = {
  // 创建练习会话
  async create(
    userId: number,
    topicId: string,
    topicName: string,
    questionCount: number,
    difficultyRange: string
  ) {
    const sql = `
      INSERT INTO practice_sessions (user_id, topic_id, topic_name, question_count, difficulty_range)
      VALUES (?, ?, ?, ?, ?)
    `;
    const [result] = await getPool().execute(sql, [
      userId,
      topicId,
      topicName,
      questionCount,
      difficultyRange,
    ]) as any;
    return result.insertId;
  },

  // 更新练习会话
  async update(
    sessionId: number,
    correctCount: number,
    totalTime: number,
    score: number,
    completed: boolean
  ) {
    const sql = `
      UPDATE practice_sessions
      SET correct_count = ?, total_time = ?, score = ?, completed = ?, completed_at = NOW()
      WHERE id = ?
    `;
    await query(sql, [correctCount, totalTime, score, completed, sessionId]);
  },

  // 获取用户练习历史
  async getUserSessions(userId: number, limit: number = 10) {
    const sql = `
      SELECT * FROM practice_sessions
      WHERE user_id = ?
      ORDER BY started_at DESC
      LIMIT ?
    `;
    return await query(sql, [userId, limit]);
  },
};

// 答题记录相关数据库操作
export const AnswerDB = {
  // 记录答题
  async create(
    sessionId: number,
    userId: number,
    topicId: string,
    questionIndex: number,
    questionStem: string,
    userAnswer: string,
    correctAnswer: string,
    isCorrect: boolean,
    timeSpent: number,
    hintsUsed: number,
    difficulty: number
  ) {
    const sql = `
      INSERT INTO answer_records (
        session_id, user_id, topic_id, question_index, question_stem,
        user_answer, correct_answer, is_correct, time_spent, hints_used, difficulty
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;
    await query(sql, [
      sessionId,
      userId,
      topicId,
      questionIndex,
      questionStem,
      userAnswer,
      correctAnswer,
      isCorrect,
      timeSpent,
      hintsUsed,
      difficulty,
    ]);
  },

  // 获取会话的所有答题记录
  async getSessionAnswers(sessionId: number) {
    const sql = `
      SELECT * FROM answer_records
      WHERE session_id = ?
      ORDER BY created_at ASC
    `;
    return await query(sql, [sessionId]);
  },

  // 获取用户已答对的题目列表（按知识点分组）
  async getCorrectAnswersByUser(userId: number, topicId?: string) {
    let sql = `
      SELECT DISTINCT topic_id, question_index
      FROM answer_records
      WHERE user_id = ? AND is_correct = 1
    `;
    const params: any[] = [userId];
    
    if (topicId) {
      sql += ` AND topic_id = ?`;
      params.push(topicId);
    }
    
    sql += ` ORDER BY topic_id, question_index`;
    
    return await query<{ topic_id: string; question_index: number }[]>(sql, params);
  },

  // 获取用户某个知识点已答对的题目索引列表
  async getCorrectQuestionIndices(userId: number, topicId: string): Promise<number[]> {
    const sql = `
      SELECT DISTINCT question_index
      FROM answer_records
      WHERE user_id = ? AND topic_id = ? AND is_correct = 1
      ORDER BY question_index
    `;
    const rows = await query<{ question_index: number }[]>(sql, [userId, topicId]);
    return rows.map(row => row.question_index);
  },
};

// 错题本相关数据库操作
export const MistakeDB = {
  // 添加错题
  async add(
    userId: number,
    topicId: string,
    topicName: string,
    questionIndex: number,
    questionStem: string,
    correctAnswer: string,
    userAnswer: string,
    solution: string,
    difficulty: number
  ) {
    const sql = `
      INSERT INTO mistake_book (
        user_id, topic_id, topic_name, question_index, question_stem,
        correct_answer, user_answer, solution, difficulty
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE
        wrong_count = wrong_count + 1,
        last_wrong_at = NOW(),
        user_answer = VALUES(user_answer)
    `;
    await query(sql, [
      userId,
      topicId,
      topicName,
      questionIndex,
      questionStem,
      correctAnswer,
      userAnswer,
      solution,
      difficulty,
    ]);
  },

  // 获取用户错题
  async getUserMistakes(userId: number, reviewed?: boolean) {
    let sql = `
      SELECT * FROM mistake_book
      WHERE user_id = ? AND mastered = FALSE
    `;
    const params: any[] = [userId];

    if (reviewed !== undefined) {
      sql += ` AND reviewed = ?`;
      params.push(reviewed);
    }

    sql += ` ORDER BY last_wrong_at DESC`;
    return await query(sql, params);
  },

  // 标记为已复习
  async markReviewed(id: number) {
    const sql = `
      UPDATE mistake_book
      SET reviewed = TRUE, reviewed_at = NOW()
      WHERE id = ?
    `;
    await query(sql, [id]);
  },

  // 标记为已掌握
  async markMastered(id: number) {
    const sql = `
      UPDATE mistake_book
      SET mastered = TRUE
      WHERE id = ?
    `;
    await query(sql, [id]);
  },

  // 获取未复习的错题索引（用于从练习中排除）
  async getUnreviewedIndices(userId: number, topicId?: string) {
    let sql = `
      SELECT topic_id, question_index
      FROM mistake_book
      WHERE user_id = ? AND reviewed = FALSE AND mastered = FALSE
    `;
    const params: any[] = [userId];

    if (topicId) {
      sql += ` AND topic_id = ?`;
      params.push(topicId);
    }

    const rows = await query(sql, params);
    return rows.map((row: any) => ({
      topic_id: row.topic_id,
      question_index: row.question_index
    }));
  },
};

// 统计相关数据库操作
export const StatsDB = {
  // 获取用户学习概览
  async getUserOverview(userId: number) {
    const sql = `
      SELECT * FROM user_learning_overview
      WHERE user_id = ?
    `;
    return await queryOne(sql, [userId]);
  },

  // 获取用户每日统计
  async getDailyStats(userId: number, days: number = 7) {
    const sql = `
      SELECT * FROM daily_statistics
      WHERE user_id = ? AND stat_date >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      ORDER BY stat_date DESC
    `;
    return await query(sql, [userId, days]);
  },

  // 获取题型统计
  async getTopicStats() {
    const sql = `SELECT * FROM topic_statistics ORDER BY user_count DESC`;
    return await query(sql);
  },

  // 生成学习报告（周报/月报）
  async generateReport(userId: number, days: number = 7) {
    // 获取时间范围内的统计
    const statsSql = `
      SELECT 
        SUM(questions_attempted) as total_questions,
        SUM(questions_correct) as total_correct,
        SUM(total_time) as total_time,
        COUNT(DISTINCT stat_date) as practice_days
      FROM daily_statistics
      WHERE user_id = ? AND stat_date >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
    `;
    const stats = await queryOne(statsSql, [userId, days]);

    // 获取知识点分析
    const topicsSql = `
      SELECT 
        topic_name,
        SUM(correct_answers) as correct_count,
        SUM(total_questions) as total_count,
        ROUND(SUM(correct_answers) * 100.0 / NULLIF(SUM(total_questions), 0), 2) as accuracy
      FROM user_progress
      WHERE user_id = ? AND last_practiced_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
      GROUP BY topic_name
      ORDER BY accuracy DESC
    `;
    const topics = await query(topicsSql, [userId, days]);

    // 获取每日趋势
    const trendSql = `
      SELECT 
        stat_date,
        questions_attempted,
        questions_correct,
        total_time,
        ROUND(questions_correct * 100.0 / NULLIF(questions_attempted, 0), 2) as accuracy
      FROM daily_statistics
      WHERE user_id = ? AND stat_date >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      ORDER BY stat_date ASC
    `;
    const trend = await query(trendSql, [userId, days]);

    return {
      summary: stats,
      topics: topics,
      trend: trend,
    };
  },
};

// 成就系统数据库操作
export const AchievementDB = {
  // 获取所有成就定义
  async getAllAchievements() {
    const sql = `
      SELECT * FROM achievements 
      ORDER BY 
        FIELD(rarity, 'common', 'rare', 'epic', 'legendary'),
        requirement_value ASC
    `;
    return await query(sql);
  },

  // 获取用户已解锁的成就
  async getUserAchievements(userId: number) {
    const sql = `
      SELECT ua.*, a.* 
      FROM user_achievements ua
      JOIN achievements a ON ua.achievement_id = a.id
      WHERE ua.user_id = ?
      ORDER BY ua.unlocked_at DESC
    `;
    return await query(sql, [userId]);
  },

  // 获取用户成就统计
  async getUserAchievementStats(userId: number) {
    const sql = `
      SELECT * FROM user_achievement_stats
      WHERE user_id = ?
    `;
    return await queryOne(sql, [userId]);
  },

  // 检查并解锁成就
  async checkAndUnlock(userId: number) {
    const sql = `CALL check_and_unlock_achievements(?)`;
    await query(sql, [userId]);
    
    // 获取新解锁的未通知成就
    const newAchievementsSql = `
      SELECT ua.*, a.*
      FROM user_achievements ua
      JOIN achievements a ON ua.achievement_id = a.id
      WHERE ua.user_id = ? AND ua.is_notified = FALSE
      ORDER BY ua.unlocked_at DESC
    `;
    const newAchievements = await query(newAchievementsSql, [userId]);
    
    // 标记为已通知
    if (newAchievements.length > 0) {
      const ids = newAchievements.map((a: any) => a.id).join(',');
      await query(`UPDATE user_achievements SET is_notified = TRUE WHERE id IN (${ids})`);
    }
    
    return newAchievements;
  },

  // 解锁特定成就
  async unlockAchievement(userId: number, achievementKey: string, progress: number = 0) {
    const sql = `
      INSERT INTO user_achievements (user_id, achievement_id, progress)
      SELECT ?, id, ? FROM achievements WHERE achievement_key = ?
      ON DUPLICATE KEY UPDATE progress = VALUES(progress)
    `;
    await query(sql, [userId, progress, achievementKey]);
  },
};

// 排行榜数据库操作
export const LeaderboardDB = {
  // 获取总排行榜（按正确题数）
  async getTotalLeaderboard(limit: number = 100) {
    const sql = `
      SELECT 
        u.id,
        u.username,
        u.display_name,
        u.avatar_url,
        COUNT(ar.id) as total_correct,
        @rank := @rank + 1 as rank_position
      FROM users u
      CROSS JOIN (SELECT @rank := 0) r
      LEFT JOIN answer_records ar ON u.id = ar.user_id AND ar.is_correct = 1
      WHERE u.is_active = 1
      GROUP BY u.id, u.username, u.display_name, u.avatar_url
      HAVING total_correct > 0
      ORDER BY total_correct DESC, u.id ASC
      LIMIT ?
    `;
    return await query(sql, [limit]);
  },

  // 获取周排行榜
  async getWeeklyLeaderboard(limit: number = 100) {
    const sql = `
      SELECT 
        u.id,
        u.username,
        u.display_name,
        u.avatar_url,
        COUNT(ar.id) as weekly_correct,
        @rank := @rank + 1 as rank_position
      FROM users u
      CROSS JOIN (SELECT @rank := 0) r
      LEFT JOIN answer_records ar ON u.id = ar.user_id 
        AND ar.is_correct = 1 
        AND ar.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
      WHERE u.is_active = 1
      GROUP BY u.id, u.username, u.display_name, u.avatar_url
      HAVING weekly_correct > 0
      ORDER BY weekly_correct DESC, u.id ASC
      LIMIT ?
    `;
    return await query(sql, [limit]);
  },

  // 获取月排行榜
  async getMonthlyLeaderboard(limit: number = 100) {
    const sql = `
      SELECT 
        u.id,
        u.username,
        u.display_name,
        u.avatar_url,
        COUNT(ar.id) as monthly_correct,
        @rank := @rank + 1 as rank_position
      FROM users u
      CROSS JOIN (SELECT @rank := 0) r
      LEFT JOIN answer_records ar ON u.id = ar.user_id 
        AND ar.is_correct = 1 
        AND ar.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
      WHERE u.is_active = 1
      GROUP BY u.id, u.username, u.display_name, u.avatar_url
      HAVING monthly_correct > 0
      ORDER BY monthly_correct DESC, u.id ASC
      LIMIT ?
    `;
    return await query(sql, [limit]);
  },

  // 获取用户在排行榜中的位置
  async getUserRank(userId: number, type: 'total' | 'weekly' | 'monthly' = 'total') {
    let timeCondition = '';
    let scoreColumn = 'total_correct';
    
    if (type === 'weekly') {
      timeCondition = 'AND ar.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)';
      scoreColumn = 'weekly_correct';
    } else if (type === 'monthly') {
      timeCondition = 'AND ar.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)';
      scoreColumn = 'monthly_correct';
    }
    
    const sql = `
      SELECT 
        COUNT(DISTINCT other.user_id) + 1 as rank_position,
        user_score.score
      FROM (
        SELECT user_id, COUNT(*) as score
        FROM answer_records
        WHERE is_correct = 1 ${timeCondition}
        GROUP BY user_id
      ) as other
      CROSS JOIN (
        SELECT COUNT(*) as score
        FROM answer_records
        WHERE user_id = ? AND is_correct = 1 ${timeCondition}
      ) as user_score
      WHERE other.score > user_score.score
    `;
    return await queryOne(sql, [userId]);
  },
};

// 导出db对象供API路由使用
export const db = {
  query,
  queryOne,
  getConnection,
  transaction,
};

export default {
  getPool,
  getConnection,
  query,
  queryOne,
  transaction,
  testConnection,
  closePool,
  UserDB,
  ProgressDB,
  SessionDB,
  AnswerDB,
  MistakeDB,
  StatsDB,
  AchievementDB,
  LeaderboardDB,
};
